package edu.tgc.service.student;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.abyss.common.service.CommonCrudService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import edu.tgc.entity.hrm.IdCade;
import edu.tgc.entity.hrm.dictionary.HrmEmployeeStatus;
import edu.tgc.entity.student.TbClass;
import edu.tgc.entity.student.TbStudent;
import edu.tgc.repository.hrm.HrmEmployeeStatusRepository;
import edu.tgc.repository.hrm.IdCadeRepository;
import edu.tgc.repository.student.TbClassRepository;
import edu.tgc.repository.student.TbStudentRepository;
@Service
public class StudentService extends CommonCrudService<TbStudent, UUID> {
    @Autowired
	private IdCadeRepository cadeDAO;
    @Autowired
    private TbStudentRepository studentDAO;
    @Autowired
    private TbClassRepository classDAO;
    @Autowired
    private HrmEmployeeStatusRepository  employeeStatusDAO;
	
    @Transactional
	public String importStu(HSSFWorkbook wb) {
    	String errorStr="";
		List<TbStudent> students=new ArrayList<>();
		List<IdCade> idCades=new ArrayList<>();
		Sheet sheet=wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		for(int i=1;i<=lastRowNum;i++){
			Row row=sheet.getRow(i);
			IdCade cade=new IdCade();
			Cell cell=row.getCell(0);//读姓名
			String name = cell.getStringCellValue().trim();
			cade.setName(name);
			cell=row.getCell(1);//读身份证号
			String idNo=cell.getStringCellValue().trim();
			cade.setIdNo(idNo);
			IdCade cade1=cadeDAO.findByIdNo(idNo);
			if(cade1!=null){
				errorStr+="第"+(i+1)+"行，身份证号重复。";
				break;
			}
			cell=row.getCell(2);
			if(cell!=null){
				String sex=cell.getStringCellValue().trim();
				cade.setSex(sex);
			}
			/*cell=row.getCell(3);
			String username=cell.getStringCellValue().trim();
			cade.setNation(nation);*/

			cell=row.getCell(4);
			if(cell!=null){
				String nation=cell.getStringCellValue().trim();
				cade.setNation(nation);
			}
			cell=row.getCell(5);
			if(cell!=null){
				Date born = cell.getDateCellValue();
				cade.setBorn(born);
			}
			cell=row.getCell(6);
			if(cell!=null){
				String address=cell.getStringCellValue().trim();
				cade.setAddress(address);
			}
			cell=row.getCell(7);
			if(cell!=null){
				Date lifeTimeB=cell.getDateCellValue();
				cade.setUserLifeB(lifeTimeB);
			}
			cell=row.getCell(8);
			if(cell!=null){
				Date lifeTimeE=cell.getDateCellValue();
				cade.setUserLifeE(lifeTimeE);
			}
			cell=row.getCell(9);
			if(cell!=null){
				String police = cell.getStringCellValue().trim();
				cade.setPolice(police);
			}
			//idCades.add(cade);
			//cadeDAO.save(cade);
			TbStudent user=new TbStudent();
			//user.setCade(cade);
			cell=row.getCell(3);
			if(cell!=null){
				String stuNo=getStringCellValue(cell).trim();
				if(!StringUtils.hasText(stuNo)){
					errorStr+="第"+(i+1)+"行，学号为空。";
					break;
				}
				TbStudent user2=studentDAO.findByStuNo(stuNo);
				if(user2!=null){
					errorStr+="第"+(i+1)+"行，学号重复。";
					break;
				}else{
					user.setStuNo(stuNo);
				}
			}
			cell=row.getCell(10);//班级
			if(cell!=null){
				String className=cell.getStringCellValue().trim();
				TbClass bj=classDAO.findByName(className);
				if(bj==null){
					errorStr+="第"+(i+1)+"行，班级名称错误。";
					break;
				}else{
					user.setBanji(bj);
				}
			}
			cell=row.getCell(11);//人员类别
			if(cell!=null){
				String statusName=cell.getStringCellValue().trim();
				HrmEmployeeStatus status= employeeStatusDAO.findByName(statusName);
				if(status!=null){
					user.setStatus(status);
				}else{
					errorStr+="第"+(i+1)+"行，人员种类错误。";
					break;
					
				}
			}
			cell=row.getCell(12);//电话号码
			if(cell!=null){
				String phone=getStringCellValue(cell).trim();
				user.setPhone(phone);
			}
			cell=row.getCell(13);//QQ号码
			if(cell!=null){
				String qq=getStringCellValue(cell).trim();
				user.setQq(qq);
			}
			//user.setPassword(passwordEncoder.encode("abcABC123"));
			idCades.add(cade);
			students.add(user);
		}
		Date now=new Date();
		for(int i=0;i<students.size();i++){
			TbStudent user=students.get(i);
			IdCade cade=idCades.get(i);
			cade.setIsUsed(true);
			cadeDAO.saveAndFlush(cade);
			user.setCade(cade);
			user.setCreateTime(now);
			user.setLastModifyTime(now);
			studentDAO.save(user);
		}
		return errorStr;
	}
    /**
     * 获取单元格数据内容为字符串类型的数据
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    private String getStringCellValue(Cell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }
    
    /**
     * 获取单元格数据内容为日期类型的数据
     * 
     * @param cell
     *            Excel单元格
     * @return String 单元格数据内容
     */
    private String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
                        + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            System.out.println("日期格式不正确!");
            e.printStackTrace();
        }
        return result;
    }

}
